import pandas, duckdb
from utils import create_windows
from time import strftime
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection


# timenow = str(strftime("%Y%m%d%H%M%S"))
def run(dwbh, dwid, zbid, zb, startday, endday, timenow):
    conn = get_connection()
    #删除已有数据
    conn.execute(f"""
            delete from T_API_PZ where accdocdate between '{startday}' and '{endday}' and LEDGERBH = '{zb}';
            delete from T_API_PZFL where ACCDOCID not in (select id from T_API_PZ);
            delete from T_API_FZYS where ACCDOCID not in (select id from T_API_PZ);
        """)
        
    # conn.execute("""
    #       create or replace table t_yl_pz as
    #              select
    #               凭证ID as id,
    #               '"""+dwbh+"""' as 单位编号,
    #               核算账簿编号 as 账簿编号,
    #               凭证编号,
    #               凭证日期,
    #               凭证类型编号,
    #               会计主管编号,
    #               substr(凭证日期, 1, 4) as 会计年度,
    #               会计期间序号,
    #               附件张数,
    #               制单人编号,
    #               '"""+timenow+"""' as 更新时间
    #               from temp_pz_main;
    #   """)
    conn.execute("""
        insert into T_API_PZ (ID, ACCDOCCODE, ACCDOCDATE, ACCDOCTYPEID, ACCMANAGERNAME, ACCORGID, ACCPERIODCODE, NUMBEROFATTCH, LEDGER, LEDGERBH, MAKERCODE, esun_api_update, dwbh, YEAR)
                select
                    gsdm||Pzly||kjqj||pzh,
                    Pzh,
                    Pzrq,
                    Pzly,
                    Sh,
                    '"""+dwid+"""',
                    substr(Pzrq, 5, 2),
                    Fjzs,
                    '"""+zbid+"""',
                    '"""+zb+"""',
                    SrID,
                    '"""+timenow+"""',
                    '"""+dwbh+"""',
                    substr(Pzrq, 1, 4)
                from GL_Pzml;
        insert into T_API_PZFL (ID, ACCDOCID, accorgid, ABSTRACT, ACCTITLECODE, AMOUNT, LENDINGDIRECTION, esun_api_update, LEDGER, LEDGERBH, DWBH, YEAR)
              select
                gsdm||Pzly||kjqj||pzh||flh,
                gsdm||Pzly||kjqj||pzh,
                '"""+dwid+"""',
                Zy,
                Kmdm,
                Je,
                case Jdbz
                when '借' then '1'
                when '贷' then '2'
                end,
                '"""+timenow+"""',
                '"""+zbid+"""',
                '"""+zb+"""',
                '"""+dwbh+"""',
                substr(kjqj, 1, 4)
               from GL_Pznr;
        insert into T_API_FZYS (ID, ACCDOCENTRYID, ACCTITLECODE, DEPTBH, RELATEDORGBH, ACCEMPLOYEEBH, WBBH, QUANTITY, UNITPRICE, FOREIGNCURRENCY, EXCHANGERATE, AMOUNT, BIZDATE, BIZCODE, OPERATOR,
                SETTLEMENT, SETTLEMENTNUMBER, APPLICATION, SETTLEMENTDATE, SPECATEBH02,
                --SM01,SM02,SM03,SM04,SM05,SM06,SM07,SM08,SM09,SM10,
                --SPECATEBH01,SPECATEBH02,SPECATEBH03,SPECATEBH04,SPECATEBH05,SPECATEBH06,SPECATEBH07,SPECATEBH08,SPECATEBH09,SPECATEBH10,SPECATEBH99,
                --BIZDICTCATEBH01,BIZDICTCATEBH02,BIZDICTCATEBH03,BIZDICTCATEBH04,BIZDICTCATEBH05,BIZDICTCATEBH06,BIZDICTCATEBH07,BIZDICTCATEBH08,BIZDICTCATEBH09,BIZDICTCATEBH10,
                esun_api_update, ACCORGID, DWBH, LEDGER, LEDGERBH, YEAR, ACCDOCID)
            select uuid(),gsdm||Pzly||kjqj||pzh||flh,Kmdm,Bmdm,Wldm,'核算人员编号',Wbdm,Sl,Dj,Wbje,Hl,Je,Wldrq,'业务号','经办人',
                '结算方式','结算号','用途','结算日期',xmdm,
                --SM01,SM02,SM03,SM04,SM05,SM06,SM07,SM08,SM09,SM10,
                --核算项目01,核算项目02,核算项目03,核算项目04,核算项目05,核算项目06,核算项目07,核算项目08,核算项目09,核算项目10,核算项目99,
                --业务字典01,业务字典02,业务字典03,业务字典04,业务字典05,业务字典06,业务字典07,业务字典08,业务字典09,业务字典10,
                '"""+timenow+"""',
                '"""+dwid+"""',
                '"""+dwbh+"""',
                '"""+zbid+"""',
                '"""+zb+"""',
                substr(kjqj, 1, 4),
                gsdm||Pzly||kjqj||pzh
            from GL_Pznr;
    """)

    conn.execute(f"""
                    delete from T_API_PZFL where ACCDOCID not in (select id from T_API_PZ);
                    delete from T_API_FZYS where ACCDOCID not in (select id from T_API_PZ);
        """)
    conn.close()
    return 200



# run(r'D:\dev\app_td\backend\rpafiles\pzfiles\xxxxxxxxxx20240101-20241231凭证导出.xlsm', timenow)